﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;
using Fuse8.ContentManagementFramework.Domain;
using Fuse8.ContentManagementFramework.SystemFacade;
using Fuse8.ContentManagementFramework.SystemFacade.Contents;

namespace Fuse8.ContentManagementFramework.DataAccess.Contents
{
    public class Sql2008ViewSchemeBuilder : IContentSchemeBuilder
    {
        private static readonly Regex sqlGoSplitter = new Regex(@"^[ \t]*GO[ \t]*[\r\n]*", RegexOptions.Compiled | RegexOptions.Multiline | RegexOptions.IgnoreCase);
        const string SqlTemplateWithMeta=
@"IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[{0}]'))
DROP VIEW [dbo].[{0}]
go
create view [dbo].[{0}]
as
select c.ContentId as Id, c.*, er.IsDeleted, er.IsLatestCompletedRevision, er.IsLatestRevision, er.IsPublished, er.PublishEndDate, er.PublishStartDate, er.WorkflowId{5}{1} from Contents c
left join EntityRevisions er on er.EntityId = c.ContentId and er.EntityTypeParameter = 'Content{3}'
left join (select p.EntityId{5}{2}
from
(select em.EntityId, em.EntityMetaValue, emf.MetaFieldName
from EntityMetaFields emf
inner join EntityMetaFieldLinks eml on emf.EntityMetaFieldId = eml.EntityMetaFieldId
left join EntityMetas em on em.EntityMetaFieldLinkId = eml.EntityMetaFieldLinkId
where eml.EntityTypeParameter = 'Content{3}') as t1
pivot 
(
	min(EntityMetaValue)
	for MetaFieldName in ({4})
) as p
) as metas on metas.EntityId = c.ContentId
where c.ContentTypeId = {3}
go";
        const string SqlTemplateWithoutMeta =
@"IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[{0}]'))
DROP VIEW [dbo].[{0}]
go
create view [dbo].[{0}]
as
select c.ContentId as Id, c.*, er.IsDeleted, er.IsLatestCompletedRevision, er.IsLatestRevision, er.IsPublished, er.PublishEndDate, er.PublishStartDate, er.WorkflowId from Contents c
left join EntityRevisions er on er.EntityId = c.ContentId and er.EntityTypeParameter = 'Content{1}'
where c.ContentTypeId = {1}
go";

        public void ChangeForContentType(int contentTypeId)
        {
            var queries = sqlGoSplitter.Split(GenerateQuery(contentTypeId));

            using (
                var connection =
                    new SqlConnection(CmsDataRepositoryConfiguration.DefineConnectionString(typeof(CmsContent))))
            {
                try
                {
                    connection.Open();
                    foreach (var s in queries)
                    {
                        if (string.IsNullOrEmpty(s))
                        {
                            continue;
                        }
                        using (var command = new SqlCommand(s, connection))
                        {
                            command.ExecuteNonQuery();
                        }
                    }
                }
                finally
                {
                    connection.Close();
                }
            }

        }

        public string GetDataEntityName(int contentTypeId)
        {
            return string.Format("cms_vw_Content{0}", contentTypeId);
        }

        internal string GenerateQuery(int contentTypeId)
        {
            var content = new CmsContent() {ContentTypeId = contentTypeId};
            string[] metas = (content.EntityMetaInformation.MetaFields ?? new List<CmsEntityMetaField>()).Select(p => p.MetaFieldName).ToArray();
            var hasMetas = metas.Length > 0;
            if (hasMetas)
            {
                return string.Format(SqlTemplateWithMeta, GetDataEntityName(contentTypeId),
                                     string.Join(", ", metas.Select(p => "metas.[" + p + "]")),
                                     string.Join(", ", metas.Select(p => "p.[" + p + "]")),
                                     contentTypeId,
                                     string.Join(", ", metas.Select(p => "[" + p + "]")),
                                     ", ");
            }

            return string.Format(SqlTemplateWithoutMeta, GetDataEntityName(contentTypeId), contentTypeId);
        }
    }
}